﻿# -*- coding:utf-8 -*-
from tkinter.messagebox import *
from tkinter import *
from student_info_sql import *
from teacher_info_sql import *
from student_achievement_sql import *
from tkinter import ttk

import matplotlib.pyplot as plt
import matplotlib.pyplot as mp
import matplotlib
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from matplotlib.figure import Figure
import numpy as np

import xlwt

global numbers


class TeacherPage(object):
    def __init__(self, number,master=None):
        global numbers
        self.root = master  # 定义内部变量root
        self.root.geometry('%dx%d' % (650, 400))  # 设置窗口大小
        self.root.resizable(0, 0)  # 防止用户调整尺寸
        numbers=number
        self.createPage()

    def createPage(self):
        self.teacher_menuPage = teacher_MenuFrame(self.root)  # 创建不同Frame


#   self.menuPage.pack() #默认显示界面


class teacher_MenuFrame(Frame):  # 继承Frame类
    def __init__(self, master=None):
        Frame.__init__(self, master)
        self.root = master  # 定义内部变量root
        self.createPage()

    def createPage(self):
        global numbers
        strs = "欢迎您！教师号为：%s 的老师！" % numbers
        Label(self.root, text=strs).place(x=210, y=0)
        Button(self.root, text='查看学生成绩单', command=self.print_student_ach, width=20, height=10).place(x=150, y=95)
        Button(self.root, text='修改学生成绩', command=self.change_ach, width=20, height=10).place(x=350, y=95)
        Button(self.root, text='查看学生成绩折线图', command=self.show_img, width=49, height=3).place(x=150, y=295)

    def show_img(self):
        self.ach_img = Tk()
        self.ach_img.title('成绩折线图')
        winWidth = 650
        winHeight = 450
        screenWidth = self.ach_img.winfo_screenwidth()
        screenHeight = self.ach_img.winfo_screenheight()
        x = int((screenWidth - winWidth) / 2)
        y = int((screenHeight - winHeight) / 2)
        # 设置窗口初始位置在屏幕居中
        self.ach_img.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x + 20, y + 20))

        fig = plt.figure(figsize=(10, 4), dpi=100)  # 图像比例
        f_plot = fig.add_subplot(111)  # 划分区域
        canvas_spice = FigureCanvasTkAgg(fig, self.ach_img)
        canvas_spice.get_tk_widget().pack()  # 随窗口大小调整而调整

        ach_name = achievement_lie_name()
        all_ach = achievement_slectTable()
        y_data = []
        name=ach_name[2:]
        x_data=[]
        ach_dict={}
 
        for j in range(len(name)):
            ach_dict[name[j]]=[]
        for i in list(all_ach):
            count=2
            x_data.append(list(i)[1])
            for j in range(len(name)):
                ach_dict[name[j]].append(list(i)[count])
                count+=1
        for k in ach_dict.values():
            y_data.append(k)
  
        # 画图
        plt.rcParams['font.sans-serif'] = 'SimHei'  # 全局字体
        f_plot.clear()  # 刷新
        plt.xlabel('科目', size=10)
        plt.ylabel('分数', size=10)
        plt.title('学生成绩折线图', size=10)
        for i in range(len(y_data)):
            plt.plot(x_data, y_data[i],label=str(name[i]))

            for a, b in zip(x_data, y_data[i]):
                plt.text(a, b, b, ha='center', va='bottom', fontsize=10)
        mp.legend(loc=9)
        plt.grid(True)  # 网格
        canvas_spice.draw()

    def change_ach(self):
        def add_new_subject():
            def sure_add():
                try:
                    Subject_name = self.subject_name.get()
                    achievement_insertData(Subject_name)
                    showinfo(title='确认', message='添加成功！')
                    self.add_menu.destroy()
                except:
                    showinfo(title='错误', message='未知错误，请重新修改！')
                    self.add_menu.destroy()

            self.change_menu.destroy()
            self.add_menu = Toplevel(self.root)
            self.add_menu.title('修改学生成绩')
            winWidth = 200
            winHeight = 200

            screenWidth = self.add_menu.winfo_screenwidth()
            screenHeight = self.add_menu.winfo_screenheight()
            x = int((screenWidth - winWidth) / 2)
            y = int((screenHeight - winHeight) / 2)
            # 设置窗口初始位置在屏幕居中
            self.add_menu.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x, y))
            # 设置窗口图标
            # root.iconbitmap("./image/icon.ico")
            # 设置窗口宽高固定
            self.add_menu.resizable(0, 0)

            self.subject_name = StringVar()
            Label(self.add_menu, text='科目名: ').place(x=10, y=30)
            Entry(self.add_menu, textvariable=self.subject_name).place(x=50, y=30)
            Button(self.add_menu, text='确认添加', command=sure_add).place(x=80, y=80)

        def sure_change():
            try:
                Student_number = self.student_number.get()
                Student_ach = self.student_ach.get()
                Subject_name = self.comvalue.get()
                if Student_ach > 100 or Student_ach < 0:
                    showinfo(title='错误', message='成绩数值错误，请重新修改！')
                else:
                    achievement_alter(Student_number, Subject_name, Student_ach)
                    showinfo(title='确认', message='修改成功！')
                    self.change_menu.destroy()
            except:
                showinfo(title='错误', message='未知错误，请重新修改！')
                self.change_menu.destroy()

        self.change_menu = Toplevel(self.root)
        self.change_menu.title('修改学生成绩')
        winWidth = 550
        winHeight = 300

        screenWidth = self.change_menu.winfo_screenwidth()
        screenHeight = self.change_menu.winfo_screenheight()
        x = int((screenWidth - winWidth) / 2)
        y = int((screenHeight - winHeight) / 2)
        # 设置窗口初始位置在屏幕居中
        self.change_menu.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x, y))
        # 设置窗口图标
        # root.iconbitmap("./image/icon.ico")
        # 设置窗口宽高固定
        self.change_menu.resizable(0, 0)

        def go(*args):
            # 处理事件，*args表示可变参数
            print(comboxlist.get())
            return comboxlist.get()  # 打印选中的值

        self.comvalue = StringVar()  # 窗体自带的文本，新建一个值
        comboxlist = ttk.Combobox(self.change_menu, textvariable=self.comvalue)  # 初始化
        achievement_lie_name()  # 获取科目列表
        b = achievement_lie_name()
        # print(b)
        a = ["无"]
        j = 0
        for i in b:
            if j >= 2:
                a.append(i)
            j += 1
        a = tuple(a)
        #         print(a)
        comboxlist["values"] = a
        comboxlist.current(0)  # 选择第一个
        comboxlist.bind("<<ComboboxSelected>>", go)  # 绑定事件,(下拉列表框被选中时，绑定go()函数)
        Label(self.change_menu, text="请选择科目").place(x=140, y=50)
        comboxlist.place(x=230, y=50)
        Label(self.change_menu, text="如果没有科目").place(x=140, y=98)
        Button(self.change_menu, text='请添加科目', command=add_new_subject, width=22).place(x=230, y=95)

        self.student_number = StringVar()
        self.student_ach = IntVar()
        Label(self.change_menu, text='学号: ').place(x=180, y=150)
        Entry(self.change_menu, textvariable=self.student_number).place(x=220, y=150)
        Label(self.change_menu, text='成绩(0~100): ').place(x=135, y=200)
        Entry(self.change_menu, textvariable=self.student_ach).place(x=220, y=200)
        Button(self.change_menu, text='确认修改', command=sure_change).place(x=250, y=250)

    def print_student_ach(self):
        def dao_subject_ach():
            try:
                b = achievement_lie_name()
                chiose = ""
                Subject_name = self.comvalue.get()
                V = self.v.get()
                if V == 0:
                    chiose = None
                if V == 1:
                    chiose = "desc"
                all_str = achievement_paixu(Subject_name, chiose)
                all_str.insert(0, b)

                def w_excel(res):
                    book = xlwt.Workbook(encoding='utf-8')  # 新建一个excel
                    sheet = book.add_sheet('sheet1')  # 新建一个sheet页
                    for row in range(0, len(res)):
                        for col in range(0, len(res[row])):
                            sheet.write(row, col, res[row][col])
                        row += 1
                        col += 1
                    book.save('%s排序成绩.xls' % Subject_name)
                    print("导出成功！")

                w_excel(all_str)
                showinfo(title='确认', message='导出成功！')
            except:
                showinfo(title='错误', message='未知错误，请重新导出！')

        def paixu():
            chiose = ""
            Subject_name = self.comvalue.get()
            V = self.v.get()
            if V == 0:
                chiose = None
            if V == 1:
                chiose = "desc"
            self.teacher_menu = Toplevel(self.root)
            self.teacher_menu.title('学生成绩单')
            winWidth = 650
            winHeight = 400
            screenWidth = self.teacher_menu.winfo_screenwidth()
            screenHeight = self.teacher_menu.winfo_screenheight()
            x = int((screenWidth - winWidth) / 2)
            y = int((screenHeight - winHeight) / 2)
            # 设置窗口初始位置在屏幕居中
            self.teacher_menu.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x - 50, y - 50))
            # 设置窗口图标
            # root.iconbitmap("./image/icon.ico")
            # 设置窗口宽高固定
            self.teacher_menu.resizable(0, 0)
            S = Scrollbar(self.teacher_menu)
            T = Text(self.teacher_menu, width=400)
            S.pack(side=RIGHT, fill=Y)
            T.pack(side=LEFT, fill=Y)
            S.config(command=T.yview)
            T.config(yscrollcommand=S.set)
            # insert的第一个参数为索引;第二个为添加的内容
            try:
                all_str = achievement_paixu(Subject_name, chiose)
                b = achievement_lie_name()
                strss = "         学号" + "       |" + "    姓名" + "    |"
                for i, j in enumerate(b):
                    if i >= 2:
                        strss += "   " + str(j) + "   |"
                strss += '\n\n'
                T.insert(END, strss)
                for i in all_str:
                    strs = ""
                    for j in i:
                        strs = strs + "    " + str(j) + "    |"
                    strs += "\n\n"
                    T.insert(END, strs)
                T.pack()
            except:
                self.teacher_menu.destroy()
                showinfo(title='错误', message='无学生成绩，请确认数据库是否有学生信息！')

        self.student_ach_menu = Toplevel(self.root)
        self.student_ach_menu.title('按科目升/降序查看成绩')
        winWidth = 300
        winHeight = 300
        screenWidth = self.student_ach_menu.winfo_screenwidth()
        screenHeight = self.student_ach_menu.winfo_screenheight()

        x = int((screenWidth - winWidth) / 2)
        y = int((screenHeight - winHeight) / 2)
        # 设置窗口初始位置在屏幕居中
        self.student_ach_menu.geometry("%sx%s+%s+%s" % (winWidth, winHeight, x, y))
        # 设置窗口图标
        # root.iconbitmap("./image/icon.ico")
        # 设置窗口宽高固定
        self.student_ach_menu.resizable(0, 0)

        def go(*args):
            # 处理事件，*args表示可变参数
            print(comboxlist.get())
            return comboxlist.get()  # 打印选中的值

        self.comvalue = StringVar()  # 窗体自带的文本，新建一个值
        comboxlist = ttk.Combobox(self.student_ach_menu, textvariable=self.comvalue)  # 初始化
        achievement_lie_name()  # 获取科目列表
        b = achievement_lie_name()
        # print(b)
        a = ["无"]
        j = 0
        for i in b:
            if j >= 2:
                a.append(i)
            j += 1
        a = tuple(a)
        comboxlist["values"] = a
        comboxlist.current(0)  # 选择第一个
        comboxlist.bind("<<ComboboxSelected>>", go)  # 绑定事件,(下拉列表框被选中时，绑定go()函数)
        Label(self.student_ach_menu, text="请选择科目:").place(x=10, y=35)
        comboxlist.place(x=80, y=35)

        self.v = IntVar()
        r1 = Radiobutton(self.student_ach_menu, variable=self.v, value=0, text="升序")
        r2 = Radiobutton(self.student_ach_menu, variable=self.v, value=1, text="降序")
        self.v.set(0)

        r1.place(x=244, y=25)
        r2.place(x=244, y=45)
        Button(self.student_ach_menu, text='查看学生成绩单', command=paixu, width=20, height=3).place(x=80, y=105)
        Button(self.student_ach_menu, text='导出某科升序/降序学生成绩单', command=dao_subject_ach, width=30, height=3).place(x=44,y=175)